
####################################################################################################
# This is a hand written C Preprocessor Macro File, run it through cpp to produce an
# sql output file.
#
####################################################################################################


# Change the quotation characters so it looks like Autotools :-)
m4_changequote(`[', `]')

m4_define([Row_ID],           [CHAR(3)])
m4_define([Long_Text],        [VARCHAR(511)])
m4_define([Short_Text],       [VARCHAR(63)])
m4_define([Timestamp],        [TIMESTAMP])

use shopping ;

drop table if exists Items ;
create table Items
(
  Item_ID               Row_ID              NOT NULL ,
  Item                  Short_Text          NOT NULL ,

  Primary Key( Item_ID )

) ;


drop table if exists Item_List ;
create table Item_List
(
  Add_Date              Timestamp           NOT NULL ,
  Item_ID               Row_ID              NOT NULL ,

  Primary Key( Add_Date , Item_ID ) ,
  Foreign Key( Item_ID ) References Items( Item_ID )

) ;


drop table if exists Comments ;
create table Comments
(
  Add_Date              Timestamp           NOT NULL ,
  Item_ID               Row_ID              NOT NULL ,
  Comment_Text          Long_Text           NOT NULL ,

  Primary Key( Add_Date , Item_ID ) ,
  Foreign Key( Add_Date , Item_ID ) References Item_List( Add_Date , Item_ID )

) ;


drop view if exists Shopping_List ;
create view Shopping_List as
  select a.Add_Date , b.Item from Item_List a , Items b where a.Item_ID = b.Item_ID ;

